package com.example.project.mapper;

import java.util.Date;
import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.project.entity.User;

/**
 * 用户表 数据层
 * 
 * @author ruoyi
 */
@Mapper
public interface UserMapper{
	
	/**
	 * 根据条件分页查询用户列表
	 * 
	 * @param page 分页参数
	 * @param user 用户信息
	 * @return 用户信息集合信息
	 */
	@Select("<script>" +
			"select u.user_id, u.dept_id, u.login_name, u.user_name, u.user_type, u.email, u.avatar, u.phonenumber, u.password, u.sex, u.salt, u.status, u.del_flag, "
			+ "u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, d.dept_name, d.leader from t_user u " +
			"left join t_dept d on u.dept_id = d.dept_id " +
			"where u.del_flag = '0' " +
			"<if test='user.userId != null and user.userId != 0'> AND u.user_id = #{user.userId} </if>" +
			"<if test='user.loginName != null and user.loginName != \"\"'> AND u.login_name like concat('%', #{user.loginName}, '%') </if>" +
			"<if test='user.status != null and user.status != \"\"'> AND u.status = #{user.status} </if>" +
			"<if test='user.phonenumber != null and user.phonenumber != \"\"'> AND u.phonenumber like concat('%', #{user.phonenumber}, '%') </if>" +
			"<if test='user.params.beginTime != null and user.params.beginTime != \"\"'> AND date_format(u.create_time,'%Y%m%d') &gt;= date_format(#{user.params.beginTime},'%Y%m%d') </if>" +
			"<if test='user.params.endTime != null and user.params.endTime != \"\"'> AND date_format(u.create_time,'%Y%m%d') &lt;= date_format(#{user.params.endTime},'%Y%m%d') </if>" +
			"<if test='user.deptId != null and user.deptId != 0'> AND (u.dept_id = #{user.deptId} OR u.dept_id IN ( SELECT t.dept_id FROM t_dept t WHERE FIND_IN_SET (#{user.deptId},ancestors) )) </if>" +
			"${user.params.dataScope}" +
			"</script>")
	@ResultType(User.class)
	List<User> selectUserList(Page<User> page, @Param("user") User user);

	/**
	 * 根据条件分页查询已配用户角色列表
	 * 
	 * @param page 分页参数
	 * @param user 用户信息
	 * @return 用户信息集合信息
	 */
	@Select("<script>" +
			"select distinct u.user_id, u.dept_id, u.login_name, u.user_name, u.user_type, u.email, u.avatar, u.phonenumber, u.status, u.create_time " +
			"from t_user u " +
			"left join t_dept d on u.dept_id = d.dept_id " +
			"left join t_user_role ur on u.user_id = ur.user_id " +
			"left join t_role r on r.role_id = ur.role_id " +
			"where u.del_flag = '0' and r.role_id = #{user.roleId} " +
			"<if test='user.loginName != null and user.loginName != \"\"'> AND u.login_name like concat('%', #{user.loginName}, '%') </if>" +
			"<if test='user.phonenumber != null and user.phonenumber != \"\"'> AND u.phonenumber like concat('%', #{user.phonenumber}, '%') </if>" +
			"${user.params.dataScope}" +
			"</script>")
	@ResultType(User.class)
	List<User> selectAllocatedList(Page<User> page, @Param("user") User user);

	/**
	 * 根据条件分页查询未分配用户角色列表
	 * 
	 * @param page 分页参数
	 * @param user 用户信息
	 * @return 用户信息集合信息
	 */
	@Select("<script>" +
			"select distinct u.user_id, u.dept_id, u.login_name, u.user_name, u.user_type, u.email, u.avatar, u.phonenumber, u.status, u.create_time " +
			"from t_user u " +
			"left join t_dept d on u.dept_id = d.dept_id " +
			"left join t_user_role ur on u.user_id = ur.user_id " +
			"left join t_role r on r.role_id = ur.role_id " +
			"where u.del_flag = '0' and (r.role_id != #{user.roleId} or r.role_id IS NULL) " +
			"and u.user_id not in (select u.user_id from t_user u inner join t_user_role ur on u.user_id = ur.user_id and ur.role_id = #{user.roleId}) " +
			"<if test='user.loginName != null and user.loginName != \"\"'> AND u.login_name like concat('%', #{user.loginName}, '%') </if>" +
			"<if test='user.phonenumber != null and user.phonenumber != \"\"'> AND u.phonenumber like concat('%', #{user.phonenumber}, '%') </if>" +
			"${user.params.dataScope}" +
			"</script>")
	@ResultType(User.class)
	List<User> selectUnallocatedList(Page<User> page, @Param("user") User user);

	/**
	 * 通过用户名查询用户
	 * 
	 * @param userName 用户名
	 * @return 用户对象信息
	 */
	@Select("select * from t_user u left join t_dept d on u.dept_id = d.dept_id left join t_user_role ur on u.user_id = ur.user_id left join t_role r on r.role_id = ur.role_id where u.login_name = #{userName} and u.del_flag = '0'")
	User selectUserByLoginName(String userName);

	/**
	 * 通过手机号码查询用户
	 * 
	 * @param phoneNumber 手机号码
	 * @return 用户对象信息
	 */
	@Select("select * from t_user u left join t_dept d on u.dept_id = d.dept_id left join t_user_role ur on u.user_id = ur.user_id left join t_role r on r.role_id = ur.role_id where u.phonenumber = #{phoneNumber} and u.del_flag = '0'")
	User selectUserByPhoneNumber(String phoneNumber);

	/**
	 * 通过邮箱查询用户
	 * 
	 * @param email 邮箱
	 * @return 用户对象信息
	 */
	@Select("select * from t_user u left join t_dept d on u.dept_id = d.dept_id left join t_user_role ur on u.user_id = ur.user_id left join t_role r on r.role_id = ur.role_id where u.email = #{email} and u.del_flag = '0'")
	User selectUserByEmail(String email);

	/**
	 * 通过用户ID查询用户
	 * 
	 * @param userId 用户ID
	 * @return 用户对象信息
	 */
	@Select("select u.user_id, u.dept_id, u.login_name, u.user_name, u.user_type, u.email, u.avatar, u.phonenumber, u.sex, u.password, u.salt, u.status, u.del_flag, u.login_ip, u.login_date, u.pwd_update_date, u.create_by, u.create_time, u.update_by, u.update_time, u.remark, d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader, d.status as dept_status, r.role_id, r.role_name, r.role_key, r.role_sort, r.data_scope, r.status as role_status from t_user u left join t_dept d on u.dept_id = d.dept_id left join t_user_role ur on u.user_id = ur.user_id left join t_role r on r.role_id = ur.role_id where u.user_id = #{userId}")
	User selectUserById(Long userId);

	/**
	 * 修改用户头像
	 * 
	 * @param userId 用户ID
	 * @param avatar 头像地址
	 * @return 结果
	 */
	@Update("update t_user set avatar = #{avatar} where user_id = #{userId}")
	int updateUserAvatar(@Param("userId") Long userId, @Param("avatar") String avatar);

	/**
	 * 修改用户状态
	 * 
	 * @param userId 用户ID
	 * @param status 状态
	 * @return 结果
	 */
	@Update("update t_user set status = #{status}, update_time = sysdate() where user_id = #{userId}")
	int updateUserStatus(@Param("userId") Long userId, @Param("status") String status);

	/**
	 * 重置用户密码
	 * 
	 * @param userId   用户ID
	 * @param password 密码
	 * @param salt     盐
	 * @return 结果
	 */
	@Update("update t_user set pwd_update_date = sysdate(), password = #{password}, salt = #{salt}, update_time = sysdate() where user_id = #{userId}")
	int resetUserPwd(@Param("userId") Long userId, @Param("password") String password, @Param("salt") String salt);

	/**
	 * 更新用户登录信息（IP和登录时间）
	 * 
	 * @param userId    用户ID
	 * @param loginIp   登录IP地址
	 * @param loginDate 登录时间
	 * @return 结果
	 */
	@Update("update t_user set login_ip = #{loginIp}, login_date = #{loginDate} where user_id = #{userId}")
	int updateLoginInfo(@Param("userId") Long userId, @Param("loginIp") String loginIp, @Param("loginDate") Date loginDate);

	/**
	 * 校验用户名称是否唯一
	 * 
	 * @param loginName 登录名称
	 * @return 结果
	 */
	@Select("select user_id, login_name from t_user where login_name=#{loginName} and del_flag = '0' limit 1")
	User checkLoginNameUnique(String loginName);

	/**
	 * 校验手机号码是否唯一
	 *
	 * @param phonenumber 手机号码
	 * @return 结果
	 */
	@Select("select user_id, phonenumber from t_user where phonenumber=#{phonenumber} and del_flag = '0' limit 1")
	User checkPhoneUnique(String phonenumber);

	/**
	 * 校验email是否唯一
	 *
	 * @param email 用户邮箱
	 * @return 结果
	 */
	@Select("select user_id, email from t_user where email=#{email} and del_flag = '0' limit 1")
	User checkEmailUnique(String email);

	@Update("update t_user set del_flag = 1 where id = #{id}")
	public int deleteUserById(Long id);
	/**
	 * 批量删除用户
	 * 
	 * @param userIds 需要删除的用户ID
	 * @return 结果
	 */
	@Update("<script>" +
			"update t_user set del_flag = '1' where user_id in " +
			"<foreach collection='array' item='userId' open='(' separator=',' close=')'>" +
			"#{userId}" +
			"</foreach>" +
			"</script>")
	int deleteUserByIds(Long[] userIds);

	@Select("<script>" +"select user_id, dept_id, login_name, user_name, user_type, email, avatar, phonenumber, password, sex, salt, status, "
			+ "del_flag, login_ip, login_date, create_by, create_time, remark from t_user where del_flag = 0 and user_id in " +
			"<foreach collection='userIds' item='userId' open='(' separator=',' close=')'>" +
			"#{userId}" +
			"</foreach>" +
			"</script>")
	List<User> selectBatchIds(List<Long> userIds);

	/**
	 * 更新用户信息
	 * 
	 * @param user 用户信息
	 * @return 结果
	 */
	@Update("<script>" +
			"update t_user " +
			"<set>" +
			"<if test='deptId != 0'>dept_id = #{deptId},</if>" +
			"<if test='userName != null and userName != \"\"'>user_name = #{userName},</if>" +
			"<if test='userType != null and userType != \"\"'>user_type = #{userType},</if>" +
			"<if test='email != null and email != \"\"'>email = #{email},</if>" +
			"<if test='phonenumber != null and phonenumber != \"\"'>phonenumber = #{phonenumber},</if>" +
			"<if test='sex != null and sex != \"\"'>sex = #{sex},</if>" +
			"<if test='avatar != null and avatar != \"\"'>avatar = #{avatar},</if>" +
			"<if test='password != null and password != \"\"'>password = #{password},</if>" +
			"<if test='salt != null and salt != \"\"'>salt = #{salt},</if>" +
			"<if test='status != null and status != \"\"'>status = #{status},</if>" +
			"<if test='loginIp != null and loginIp != \"\"'>login_ip = #{loginIp},</if>" +
			"<if test='loginDate != null'>login_date = #{loginDate},</if>" +
			"<if test='pwdUpdateDate != null'>pwd_update_date = #{pwdUpdateDate},</if>" +
			"<if test='updateBy != null and updateBy != \"\"'>update_by = #{updateBy},</if>" +
			"<if test='remark != null'>remark = #{remark},</if>" +
			"update_time = sysdate()" +
			"</set>" +
			"where user_id = #{userId}" +
			"</script>")
	int updateUser(User user);

	/**
	 * 新增用户信息
	 * 
	 * @param user 用户信息
	 * @return 结果
	 */
	@Insert("<script>" +
			"insert into t_user(" +
			"<if test='userId != null and userId != 0'>user_id,</if>" +
			"<if test='deptId != null and deptId != 0'>dept_id,</if>" +
			"<if test='loginName != null and loginName != \"\"'>login_name,</if>" +
			"<if test='userName != null and userName != \"\"'>user_name,</if>" +
			"<if test='userType != null and userType != \"\"'>user_type,</if>" +
			"<if test='email != null and email != \"\"'>email,</if>" +
			"<if test='avatar != null and avatar != \"\"'>avatar,</if>" +
			"<if test='phonenumber != null and phonenumber != \"\"'>phonenumber,</if>" +
			"<if test='sex != null and sex != \"\"'>sex,</if>" +
			"<if test='password != null and password != \"\"'>password,</if>" +
			"<if test='salt != null and salt != \"\"'>salt,</if>" +
			"<if test='status != null and status != \"\"'>status,</if>" +
			"<if test='pwdUpdateDate != null'>pwd_update_date,</if>" +
			"<if test='createBy != null and createBy != \"\"'>create_by,</if>" +
			"<if test='remark != null and remark != \"\"'>remark,</if>" +
			"create_time" +
			")values(" +
			"<if test='userId != null and userId != \"\"'>#{userId},</if>" +
			"<if test='deptId != null and deptId != \"\"'>#{deptId},</if>" +
			"<if test='loginName != null and loginName != \"\"'>#{loginName},</if>" +
			"<if test='userName != null and userName != \"\"'>#{userName},</if>" +
			"<if test='userType != null and userType != \"\"'>#{userType},</if>" +
			"<if test='email != null and email != \"\"'>#{email},</if>" +
			"<if test='avatar != null and avatar != \"\"'>#{avatar},</if>" +
			"<if test='phonenumber != null and phonenumber != \"\"'>#{phonenumber},</if>" +
			"<if test='sex != null and sex != \"\"'>#{sex},</if>" +
			"<if test='password != null and password != \"\"'>#{password},</if>" +
			"<if test='salt != null and salt != \"\"'>#{salt},</if>" +
			"<if test='status != null and status != \"\"'>#{status},</if>" +
			"<if test='pwdUpdateDate != null'>#{pwdUpdateDate},</if>" +
			"<if test='createBy != null and createBy != \"\"'>#{createBy},</if>" +
			"<if test='remark != null and remark != \"\"'>#{remark},</if>" +
			"sysdate()" +
			")" +
			"</script>")
	@Options(useGeneratedKeys = true, keyProperty = "userId")
	int insertUser(User user);
	
	@Select("select user_id, dept_id, login_name, user_name, user_type, email, avatar, phonenumber, password, sex, salt, status, "
			+ "del_flag, login_ip, login_date, create_by, create_time, remark from t_user where del_flag = 0 and user_id != 1")
	List<User> selectUserAll();

	@Select("select user_id, dept_id, login_name, user_name, user_type, email, avatar, phonenumber, password, sex, salt, status, "
			+ "del_flag, login_ip, login_date, create_by, create_time, remark from t_user where del_flag = 0 and user_id = 1")
	User getAdministrator();
}